The Hidden Pitfalls of Excel Column Widths

TLDR

• Excel column width calculation is not an absolute value; it depends on the file's "default font" and "font size."
• Fonts set for individual cells do not affect column width; only changing Excel's default font settings does.
• When exporting Excel files using NPOI or EPPlus, failing to standardize the default font will result in column widths that do not match expectations.
• The default font for NPOI 2.7.1 is Calibri, size 11.
• After changing the default font settings in the Excel software itself, you must restart Excel for the changes to take effect.
• It is recommended to explicitly set the default font of the Workbook in your code to ensure consistent Excel column widths across different environments.

Excel Column Width Calculation Mechanism

When you might encounter this issue: When developers use NPOI or EPPlus to export Excel files and find that even with the same column width values set, the output files have inconsistent column widths when viewed on different computers or compared to a client's template.

Excel's column width calculation logic is based on the width of the "default font." Even if a developer applies different font styles to specific cells, the basis for the column width calculation remains the Workbook's default font. If the default font is not explicitly specified in the code, the library will use its default value (e.g., NPOI defaults to Calibri 11), leading to display discrepancies across environments or files.

Impact of Default Font on Column Width and Row Height

When you might encounter this issue: When a user changes the default font settings of the Excel software, or when the default font of the Excel file generated by the program differs from the client's template.

• Column Width Differences: With the same column width value (e.g., 8.04), the larger the default font size, the wider the actual displayed column width.
• Row Height Differences: Row height adjusts automatically based on the default font size. To maintain a consistent visual effect, you must manually adjust the row height value.
• Activation Constraints: After changing the default font settings in the Excel software, you must close all open Excel windows and create a new file for the settings to take effect.

Setting the Default Font Using NPOI

When you might encounter this issue: When using NPOI for exports and you need to ensure that the output Excel column widths are perfectly consistent with a specific template.

You can modify the default font in NPOI as follows:

using IWorkbook workbook = new XSSFWorkbook();
IFont defaultFont = workbook.GetFontAt(0);
// Modify default font settings
defaultFont.FontName = "Microsoft JhengHei";
defaultFont.FontHeightInPoints = 20;

workbook.CreateSheet()
    .CreateRow(0)
    .CreateCell(0)
    .SetCellValue("Test");

using FileStream fileStream = new("Test.xlsx", FileMode.Create, FileAccess.Write);
workbook.Write(fileStream);

WARNING

Modifying defaultFont in NPOI may result in the font not being fully applied to all cell styles. It is recommended to perform actual tests and fine-tuning for column widths during development.

Setting the Default Font Using EPPlus

When you might encounter this issue: When using EPPlus for Excel processing and you wish to control the default font via code to achieve precise layout.

EPPlus allows direct access to Styles.Fonts to set the default font:

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using ExcelPackage package = new();
ExcelFontXml defaultFont = package.Workbook.Styles.Fonts[0];

// Set default font and size
defaultFont.Name = "Microsoft JhengHei";
defaultFont.Size = 20;

ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Sheet1");
sheet.Cells[1,1,1,1].Value = "Test";

using FileStream fileStream = new("Test.xlsx", FileMode.Create, FileAccess.Write);
package.SaveAs(fileStream);

Based on testing, the accuracy and application effect of column width calculations are more stable in EPPlus after setting the default font.

Change Log

• 2025-08-31 Initial version created.